package tools;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Properties;

import tools.model.SqlDBColumn;
import tools.model.SqlDBConfig;
import tools.model.SqlDBIndex;
import tools.model.SqlDBTable;

/**
 * 数据库表结构对比工具
 * 
 * @author wumingli
 */
public class SqlDBStructCompareTool {

	public static void main(String[] args) {
		System.out.println("******************* start ********************");

		Properties properties;
		try {
			properties = loadProperties(args.length > 0 ? args[0] : "SqlDBStructCompareTool.properties");
		} catch (Exception e) {
			System.out.println("******************* error *********************");
			e.printStackTrace();
			return;
		
		}
		SqlDBConfig proDbConfig = getConfigByName("pro", properties);
		SqlDBConfig devDbConfig = getConfigByName("dev", properties);

		SqlDBStructCompareTool tool = new SqlDBStructCompareTool(proDbConfig, devDbConfig);
		
		try {
			tool.compare();
			tool.genSql();
		} catch (Exception e) {
			System.out.println("******************** error *******************");
			e.printStackTrace();
			return;
		}

		System.out.println("-- ********************* done *******************");
	}

	private static Properties loadProperties(String file) throws FileNotFoundException, IOException {
		Properties properties = new Properties();
		properties.load(new FileInputStream(file));
		return properties;
	}

	private static SqlDBConfig getConfigByName(String name, Properties properties) {
		SqlDBConfig config = new SqlDBConfig();
		config.setName(name);
		config.setDriverClass(properties.getProperty(name + "-driverClass"));
		config.setJdbcUrl(properties.getProperty(name + "-jdbcUrl"));
		config.setUserName(properties.getProperty(name + "-userName"));
		config.setPassword(properties.getProperty(name + "-password"));
		return config;
	}

	private SqlDBConfig proDbConfig;
	private SqlDBConfig devDbConfig;
	private List<SqlDBTable> newTableList;
	private List<SqlDBTable> modifyTableList;
	private String proDbName;

	public SqlDBStructCompareTool(SqlDBConfig proDbConfig, SqlDBConfig devDbConfig) {
		this.proDbConfig = proDbConfig;
		this.devDbConfig = devDbConfig;
	}

	private void compare() throws Exception {
		this.proDbName = getDBName(proDbConfig);
		this.newTableList = new ArrayList<>();
		this.modifyTableList = new ArrayList<>();

		List<SqlDBTable> proTableList = getTableList(proDbConfig);
		List<SqlDBTable> devTableList = getTableList(devDbConfig);

		// compare
		for (SqlDBTable devTable : devTableList) {
			SqlDBTable proTable = getTableByName(proTableList, devTable.getTableName());
			if (proTable == null) {
				newTableList.add(devTable);
			}
			else {
				SqlDBTable modifyColumns= compareColumns(proTable, devTable);
				if(modifyColumns.getColumns().size() > 0) {
					modifyTableList.add(modifyColumns);
				}
				SqlDBTable modifyIndex = compareIndexes(proTable, devTable);
				if(modifyIndex.getIndexes().size() > 0) {
					modifyTableList.add(modifyIndex);
				}
			}
		}
		
		System.out.println("************ compare result start ************");

		// desc
		System.out.println("-- ------------ create tables --------------");
		for (SqlDBTable sqlDBTable : newTableList) {
			System.out.println("create table:" + sqlDBTable.getTableName());
		}
		
		System.out.println("-- ------------ modify columns --------------");
		for (SqlDBTable sqlDBTable : modifyTableList) {
			if (sqlDBTable.getColumns() != null) {
				System.out.println("modify table: " + sqlDBTable.getTableName());
				for (SqlDBColumn sqlDBColumn : sqlDBTable.getColumns()) {
					if (sqlDBColumn.isDelete()) {
						System.out.println("     drop column: " + sqlDBColumn.getField());
					}
					else if (sqlDBColumn.isCreate()) {
						System.out.println("     create column: " + sqlDBColumn.getField());
					}
					else {
						System.out.println("     modify column: " + sqlDBColumn.getField());
					}
				}
			}
		}
		
		System.out.println("-- ------------ modify indexes --------------");
		for (SqlDBTable sqlDBTable : modifyTableList) {
			if (sqlDBTable.getIndexes() != null) {
				System.out.println("modify table: " + sqlDBTable.getTableName());
				for (SqlDBIndex sqlDBIndex : sqlDBTable.getIndexes()) {
					if (sqlDBIndex.isDelete()) {
						System.out.println("--     drop index: " + sqlDBIndex.getKeyName() + "(" + sqlDBIndex.getColumnNameStr()+ ")");
					}
					else if (sqlDBIndex.isCreate()) {
						System.out.println("     create index: " + sqlDBIndex.getKeyName() + "(" + sqlDBIndex.getColumnNameStr()+ ")");
					}
					else {
						System.out.println("     modify index: " + sqlDBIndex.getKeyName() + "(" + sqlDBIndex.getColumnNameStr()+ ")");
					}
				}
			}
		}

		System.out.println("************** compare result end ************");
		System.out.println();
		System.out.println();
	}

	private void genSql() throws Exception {
		System.out.println("-- ************** sql start *******************");
		System.out.println("use "+proDbName+";");

		System.out.println("-- ------------ create tables --------------");
		for (SqlDBTable sqlDBTable : newTableList) {
			System.out.println(sqlDBTable.getTableCreate()+";");
			System.out.println();
		}
		
		System.out.println("-- ------------ modify columns --------------");
		for (SqlDBTable sqlDBTable : modifyTableList) {
			if (sqlDBTable.getColumns() != null) {
				System.out.println("-- modify table: " + sqlDBTable.getTableName());
				for (SqlDBColumn sqlDBColumn : sqlDBTable.getColumns()) {
					if (sqlDBColumn.isDelete()) {
						System.out.println("-- " + sqlDBColumn.toString());
					}
					else {
						System.out.println(sqlDBColumn.toString());
					}
				}
				System.out.println();
			}
		}
		
		System.out.println("-- ------------ modify indexes --------------");
		for (SqlDBTable sqlDBTable : modifyTableList) {
			if (sqlDBTable.getIndexes() != null) {
				System.out.println("-- modify table index: " + sqlDBTable.getTableName());
				for (SqlDBIndex sqlDBIndex : sqlDBTable.getIndexes()) {
					if (sqlDBIndex.isDelete()) {
						System.out.println("-- " + sqlDBIndex.toString());
					}
					else {
						System.out.println(sqlDBIndex.toString());
					}
				}
				System.out.println();
			}
		}

		System.out.println("-- ************** sql end *********************");
		System.out.println();
		System.out.println();
	}

	private SqlDBTable compareColumns(SqlDBTable proTable, SqlDBTable devTable) {
		SqlDBTable rs = new SqlDBTable();
		List<SqlDBColumn> columns = new ArrayList<>();

		// get change
		for (SqlDBColumn devColumn : devTable.getColumns()) {
			SqlDBColumn proColumn = getColumnByField(proTable.getColumns(), devColumn.getField());

			if (proColumn == null) {
				devColumn.setCreate(true);
				columns.add(devColumn);
			}
			else if (!eqStr(proColumn.getType(), devColumn.getType())
					|| proColumn.getNull() != devColumn.getNull()
					|| !eqStr(proColumn.getDefaultValue(), devColumn.getDefaultValue())
					|| !eqStr(proColumn.getExtra(), devColumn.getExtra())) {
				devColumn.setModify(true);
				columns.add(devColumn);
			}
		}

		// get delete
		for (SqlDBColumn proColumn : proTable.getColumns()) {
			SqlDBColumn devColumn = getColumnByField(devTable.getColumns(), proColumn.getField());
			if (devColumn == null) {
				proColumn.setDelete(true);
				columns.add(proColumn);
			}
		}

		rs.setTableName(proTable.getTableName());
		rs.setColumns(columns);
		return rs;
	}

	private boolean eqStr(String v1, String v2) {
		if(v1 != null) {
			return v1.equals(v2);
		}
		if(v2 != null) {
			return v2.equals(v1);
		}
		return v1 == v2;
	}

	private SqlDBTable compareIndexes(SqlDBTable proTable, SqlDBTable devTable) {
		SqlDBTable rs = new SqlDBTable();
		List<SqlDBIndex> indexes = new ArrayList<>();

		// get change
		for (SqlDBIndex devIndex : devTable.getIndexes()) {
			SqlDBIndex proIndex = getIndexByKeyNameColumnName(proTable.getIndexes(), devIndex.getKeyName());

			if (proIndex == null) {
				devIndex.setCreate(true);
				indexes.add(devIndex);
			}
			else if (proIndex.isUnique() != devIndex.isUnique()
					|| proIndex.isNull() != devIndex.isNull()
					|| !proIndex.getColumnNameStr().equals(devIndex.getColumnNameStr())
					|| !eqStr(proIndex.getIndexType(), devIndex.getIndexType())) {
				devIndex.setModify(true);
				indexes.add(devIndex);
			}
		}

		// get delete
		for (SqlDBIndex proIndex : proTable.getIndexes()) {
			SqlDBIndex devIndex = getIndexByKeyNameColumnName(devTable.getIndexes(), proIndex.getKeyName());
			if (devIndex == null) {
				proIndex.setDelete(true);
				indexes.add(proIndex);
			}
		}

		rs.setTableName(proTable.getTableName());
		rs.setIndexes(indexes);
		return rs;
	}

	private SqlDBIndex getIndexByKeyNameColumnName(List<SqlDBIndex> indexes, String keyName) {
		for (SqlDBIndex index : indexes) {
			if (keyName.equalsIgnoreCase(index.getKeyName())) {
				return index;
			}
		}
		return null;
	}

	private SqlDBColumn getColumnByField(List<SqlDBColumn> list, String field) {
		for (SqlDBColumn column : list) {
			if (field.equalsIgnoreCase(column.getField())) {
				return column;
			}
		}
		return null;
	}

	private SqlDBTable getTableByName(List<SqlDBTable> proTableList, String tableName) {
		for (SqlDBTable table : proTableList) {
			if (tableName.equalsIgnoreCase(table.getTableName())) {
				return table;
			}
		}
		return null;
	}

	private String getDBName(SqlDBConfig dbConfig) throws Exception {
		Connection conn = getConnection(dbConfig);
		try {
			return conn.getCatalog();
		} finally {
			conn.close();
		}
	}

	private List<SqlDBTable> getTableList(SqlDBConfig dbConfig) throws Exception {
		Connection conn = getConnection(dbConfig);
		List<SqlDBTable> tables = new ArrayList<>();

		try {
			// get table
			List<HashMap<String, Object>> result = executeQuery(conn, "show tables");
			for (HashMap<String, Object> map : result) {
				String tableName = map.values().toArray(new String[1])[0];
				String tableCreate = (String) getFirstValue("Create Table", executeQuery(conn, "show create table " + tableName));
				tableCreate = tableCreate.replaceAll(" AUTO_INCREMENT\\s*=\\s*\\d+", "");
				
				SqlDBTable table = new SqlDBTable();
				table.setTableName(tableName);
				table.setTableCreate(tableCreate);
				tables.add(table);
			}

			// get column
			for (SqlDBTable table : tables) {
				List<HashMap<String, Object>> colRs = executeQuery(conn, "show columns from " + table.getTableName());
				List<SqlDBColumn> columns = new ArrayList<>();
				for (HashMap<String, Object> map : colRs) {
					String commentSql = "select COLUMN_NAME,COLUMN_COMMENT from INFORMATION_SCHEMA.Columns where table_name='"+table.getTableName()+"' and table_schema='"+conn.getCatalog()+"'";
					String comment = (String) getFirstValue("COLUMN_COMMENT", executeQuery(conn, commentSql));
							
					SqlDBColumn column = new SqlDBColumn();
					column.setTableName(table.getTableName());
					column.setField((String) map.get("Field"));
					column.setType((String) map.get("Type"));
					column.setNull("YES".equals(map.get("Null")));
					column.setKey((String) map.get("Key"));
					column.setDefaultValue((String) map.get("Default"));
					column.setExtra((String) map.get("Extra"));
					column.setComment(comment);
					columns.add(column);
				}
				table.setColumns(columns);
			}
			
			// get index
			for (SqlDBTable table : tables) {
				List<HashMap<String, Object>> idxRs = executeQuery(conn, "show index from " + table.getTableName());
				List<SqlDBIndex> indexes = new ArrayList<>();
				SqlDBIndex prevIndex = null;
				
				for (HashMap<String, Object> map : idxRs) {
					String columnName = (String) map.get("Column_name");
					long seqInIndex = (Long) map.get("Seq_in_index");
					Long subPart = (Long) map.get("Sub_part");
					
					if(subPart == null) {
						columnName = "`"+columnName+"`";
					} else {
						columnName = "`"+columnName+"`("+subPart+")";
					}
					
					SqlDBIndex index = prevIndex;
					
					if(seqInIndex == 1) {
						index = new SqlDBIndex();
						index.setColumnNames(new ArrayList<String>());
						index.setTableName(table.getTableName());
						index.setUnique(map.get("Non_unique").equals(0));
						index.setKeyName((String) map.get("Key_name"));
						index.setSubPart((Long) map.get("Sub_part"));
						index.setNull(map.get("Null").equals("YES"));
						index.setIndexType((String) map.get("Index_type"));
						indexes.add(index);
					}
					
					index.getColumnNames().add(columnName);
					prevIndex = index;

				}
				table.setIndexes(indexes);
			}
			
		} finally {
			conn.close();
		}

		return tables;
	}

	private Object getFirstValue(String columnLabel, List<HashMap<String, Object>> result) {
		return result.size() > 0 ? result.get(0).get(columnLabel) : null;
	}

	private List<HashMap<String, Object>> executeQuery(Connection conn, String sql) throws SQLException {
		Statement statement = conn.createStatement();
		ResultSet rsSet = statement.executeQuery(sql);
		List<HashMap<String, Object>> rs = new ArrayList<>();
		try {
			ResultSetMetaData meta = rsSet.getMetaData();
			int count = meta.getColumnCount();
			while (rsSet.next()) {
				HashMap<String, Object> map = new HashMap<>();
				for (int i = 1; i <= count; i++) {
					map.put(meta.getColumnLabel(i), rsSet.getObject(i));
				}
				rs.add(map);
			}
			return rs;
		} finally {
			try {
				rsSet.close();
			} finally {
				statement.close();
			}
		}
	}

	private Connection getConnection(SqlDBConfig config) throws Exception {
		Connection conn;
		try {
			Class.forName(config.getDriverClass());
			conn = DriverManager.getConnection(config.getJdbcUrl(), config.getUserName(), config.getPassword());
			System.out.println("连接" + config.getName() + "成功..");
		} catch (Exception e) {
			System.out.println("连接" + config.getName() + "失败!");
			throw e;
		}
		return conn;
	}
}
